Skip Headers

Oracle® OLAP Developer's Guide to the OLAP API 10g Release 2 (10.2) Part Number B14347-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 11 Creating Dynamic Queries

This chapter describes the Oracle OLAP API `Template` class and its related classes, which you use to create dynamic queries. This chapter also provides examples of implementations of those classes.

This chapter includes the following topics:

- About Template Objects

- Overview of Template and Related Classes

- Designing and Implementing a Template

For the complete code of the examples in this chapter, see the example programs available from the Overview of the Oracle OLAP Java API Reference.

## About Template Objects

The `Template` class is the basis of a very powerful feature of the Oracle OLAP API. You use `Template` objects to create modifiable `Source` objects. With those `Source` objects, you can create dynamic queries that can change in response to end-user selections. `Template` objects also offer a convenient way for you to translate user-interface elements into OLAP API operations and objects.

These features are briefly described in the following section. The rest of this chapter describes the `Template` class and the other classes you use to create dynamic `Source` objects. For information on the `Transaction` objects that you use to make changes to the dynamic `Source` and to either save or discard those changes, see Chapter 8, "Using a TransactionProvider".

### About Creating a Dynamic Source

The main feature of a `Template` is its ability to produce a dynamic `Source`. That ability is based on two of the other objects that a `Template` uses: instances of the `DynamicDefinition` and `MetadataState` classes.

When a `Source` is created, a `SourceDefinition` is automatically created. The `SourceDefinition` has information about how the `Source` was created. Once created, the `Source` and its `SourceDefinition` are paired immutably. The `getSource` method of a `SourceDefinition` returns its paired `Source`.

`DynamicDefinition` is a subclass of `SourceDefinition`. A `Template` creates a `DynamicDefinition`, which acts as a proxy for the `SourceDefinition` of the `Source` produced by the `Template`. This means that instead of always getting the same immutably paired `Source`, the `getSource` method of the `DynamicDefinition` gets whatever `Source` is currently produced by the `Template`. The instance of the `DynamicDefinition` does not change even though the `Source` that it gets is different.

The `Source` that a `Template` produces can change because the values, including other `Source` objects, that the `Template` uses to create the `Source` can change. A `Template` stores those values in a `MetadataState`. A `Template` provides methods to get the current state of the `MetadataState`, to get or set a value, and to set the state. You use those methods to change the data values the `MetadataState` stores.

You use a `DynamicDefinition` to get the `Source` produced by a `Template`. If your application changes the state of the values that the `Template` uses to create the `Source`, for example, in response to end-user selections, then the application uses the same `DynamicDefinition` to get the `Source` again, even though the new `Source` defines a result set different than the previous `Source`.

The `Source` produced by a `Template` can be the result of a series of `Source` operations that create other `Source` objects, such as a series of selections, sorts, calculations, and joins. You put the code for those operations in the `generateSource` method of a `SourceGenerator` for the `Template`. That method returns the `Source` produced by the `Template`. The operations use the data stored in the `MetadataState`.

You might build an extremely complex query that involves the interactions of dynamic `Source` objects produced by many different `Template` objects. The end result of the query building is a `Source` that defines the entire complex query. If you change the state of any one of the `Template` objects that you used to create the final `Source`, then the final `Source` represents a result set different than that of the previous `Source`. You can thereby modify the final query without having to reproduce all of the operations involved in defining the query.

### About Translating User Interface Elements into OLAP API Objects

You design `Template` objects to represent elements of the user interface of an application. Your `Template` objects turn the selections that the end user makes into OLAP API query-building operations that produce a `Source`. You then create a `Cursor` to fetch the result set defined by the `Source` from Oracle OLAP. You get the values from the `Cursor` and display them to the end user. When an end user makes changes to the selections, you change the state of the `Template`. You then get the `Source` produced by the `Template`, create a new `Cursor`, get the new values, and display them.

## Overview of Template and Related Classes

In the OLAP API, several classes work together to produce a dynamic `Source`. In designing a `Template`, you must implement or extend the following:

- The `Template` abstract class

- The `MetadataState` interface

- The `SourceGenerator` interface

Instances of those three classes, plus instances of other classes that Oracle OLAP creates, work together to produce the `Source` that the `Template` defines. The classes that Oracle OLAP provides, which you create by calling factory methods, are the following:

- `DataProvider`

- `DynamicDefinition`

### What Is the Relationship Between the Classes That Produce a Dynamic Source?

The classes that produce a dynamic `Source` work together as follows:

- A `Template` has methods that create a `DynamicDefinition` and that get and set the current state of a `MetadataState`. An extension to the `Template` abstract class adds methods that get and set the values of fields on the `MetadataState`.

- The `MetadataState` implementation has fields for storing the data to use in generating the `Source` for the `Template`. When you create a new `Template`, you pass the `MetadataState` to the constructor of the `Template`. When you call the `getSource` method of the `DynamicDefinition`, the `MetadataState` is passed to the `generateSource` method of the `SourceGenerator`.

- The `DataProvider` is used in creating a `Template` and by the `SourceGenerator` in creating new `Source` objects.

- The `SourceGenerator` implementation has a `generateSource` method that uses the current state of the data in the `MetadataState` to produce a `Source` for the `Template`. You pass in the `SourceGenerator` to the `createDynamicDefinition` method of the `Template` to create a `DynamicDefinition`.

- The `DynamicDefinition` has a `getSource` method that gets the `Source` produced by the `SourceGenerator`. The `DynamicDefinition` serves as a proxy for the immutably paired `SourceDefinition` of that `Source`.

### Template Class

You use a `Template` to produce a modifiable `Source`. A `Template` has methods for creating a `DynamicDefinition` and for getting and setting the current state of the `Template`. In extending the `Template` class, you add methods that provide access to the fields on the `MetadataState` for the `Template`. The `Template` creates a `DynamicDefinition` that you use to get the `Source` produced by the `SourceGenerator` for the `Template`.

For an example of a `Template` implementation, see Example 11-1.

### MetadataState Interface

An implementation of the `MetadataState` interface stores the current state of the values for a `Template`. A `MetadataState` must include a `clone` method that creates a copy of the current state.

When instantiating a new `Template`, you pass a `MetadataState` to the `Template` constructor. The `Template` has methods for getting and setting the values stored by the `MetadataState`. The `generateSource` method of the `SourceGenerator` for the `Template` uses the `MetadataState` when the method produces a `Source` for the `Template`.

For an example of a `MetadataState` implementation, see Example 11-2.

### SourceGenerator Interface

An implementation of `SourceGenerator` must include a `generateSource` method, which produces a `Source` for a `Template`. A `SourceGenerator` must produce only one type of `Source`, such as a `BooleanSource`, a `NumberSource`, or a `StringSource`. In producing the `Source`, the `generateSource` method uses the current state of the data represented by the `MetadataState` for the `Template`.

To get the `Source` produced by the `generateSource` method, you create a `DynamicDefinition` by passing the `SourceGenerator` to the `createDynamicDefinition` method of the `Template`. You then get the `Source` by calling the `getSource` method of the `DynamicDefinition`.

A `Template` can create more than one `DynamicDefinition`, each with a differently implemented `SourceGenerator`. The `generateSource` methods of the different `SourceGenerator` objects use the same data, as defined by the current state of the `MetadataState` for the `Template`, to produce `Source` objects that define different queries.

For an example of a `SourceGenerator` implementation, see Example 11-3.

### DynamicDefinition Class

`DynamicDefinition` is a subclass of `SourceDefinition`. You create a `DynamicDefinition` by calling the `createDynamicDefinition` method of a `Template` and passing it a `SourceGenerator`. You get the `Source` produced by the `SourceGenerator` by calling the `getSource` method of the `DynamicDefinition`.

A `DynamicDefinition` created by a `Template` is a proxy for the `SourceDefinition` of the `Source` produced by the `SourceGenerator`. The `SourceDefinition` is immutably paired to its `Source`. If the state of the `Template` changes, then the `Source` produced by the `SourceGenerator` is different. Because the `DynamicDefinition` is a proxy, you use the same `DynamicDefinition` to get the new `Source` even though that `Source` has a different `SourceDefinition`.

The `getCurrent` method of a `DynamicDefinition` returns the `SourceDefinition` immutably paired to the `Source` that the `generateSource` method currently returns. For an example of the use of a `DynamicDefinition`, see Example 11-4.

## Designing and Implementing a Template

The design of a `Template` reflects the query-building elements of the user interface of an application. For example, suppose you want to develop an application that allows the end user to create a query that requests a number of values from the top or bottom of a list of values. The values are from one dimension of a measure. The other dimensions of the measure are limited to single values.

The user interface of your application has a dialog box that allows the end user to do the following:

- Select a radio button that specifies whether the data values should be from the top or bottom of the range of values.

- Select a measure from a drop-down list of measures.

- Select a number from a field. The number specifies the number of data values to display.

- Select one of the dimensions of the measure as the base of the data values to display. For example, if the user selects the product dimension, then the query specifies some number of products from the top or bottom of the list of products. The list is determined by the measure and the selected values of the other dimensions.

- Click a button to bring up a Single Selections dialog box through which the end user selects the single values for the other dimensions of the selected measure. After selecting the values of the dimensions, the end user clicks an OK button on the second dialog box and returns to the first dialog box.

- Click an OK button to generate the query. The results of the query appear.

To generate a `Source` that represents the query that the end user creates in the first dialog box, you design a `Template` called `TopBottomTemplate`. You also design a second `Template`, called `SingleSelectionTemplate`, to create a `Source` that represents the end user's selections of single values for the dimensions other than the base dimension. The designs of your `Template` objects reflect the user interface elements of the dialog boxes.

In designing the `TopBottomTemplate` and its `MetadataState` and `SourceGenerator`, you do the following:

- Create a class called `TopBottomTemplate` that extends `Template`. To the class, you add methods that get the current state of the `Template`, set the values specified by the user, and then set the current state of the `Template`.

- Create a class called `TopBottomTemplateState` that implements `MetadataState`. You provide fields on the class to store values for the `SourceGenerator` to use in generating the `Source` produced by the `Template`. The values are set by methods of the `TopBottomTemplate`.

- Create a class called `TopBottomTemplateGenerator` that implements `SourceGenerator`. In the `generateSource` method of the class, you provide the operations that create the `Source` specified by the end user's selections.

Using your application, an end user selects units sold as the measure and products as the base dimension in the first dialog box. From the Single Selections dialog box, the end user selects the Asia Pacific region, the first quarter of 2001, and the direct sales channel as the single values for each of the remaining dimensions.

The query that the end user has created requests the ten products that have the highest total amount of units sold through the direct sales channel to customers in the Asia Pacific region during the calendar year 2001.

For examples of implementations of the `TopBottomTemplate`, `TopBottomTemplateState`, and `TopBottomTemplateGenerator` classes, and an example of an application that uses them, see Example 11-1, Example 11-2, Example 11-3, and Example 11-4. The `TopBottomTemplateState` and `TopBottomTemplateGenerator` classes are implemented as inner classes of the `TopBottomTemplate` outer class.

### Implementing the Classes for a Template

Example 11-1 is an implementation of the `TopBottomTemplate` class.

Example 11-1 Implementing a Template

    package globalExamples;

    import oracle.olapi.data.source.DataProvider;
    import oracle.olapi.data.source.DynamicDefinition;
    import oracle.olapi.data.source.Source;
    import oracle.olapi.data.source.SourceGenerator;
    import oracle.olapi.data.source.Template;
    import oracle.olapi.transaction.metadataStateManager.MetadataState;

    /**
     * Creates a TopBottomTemplateState, a TopBottomTemplateGenerator,
     * and a DynamicDefinition. Gets the current state of the
     * TopBottomTemplateState and the values it stores. Sets the data
     * values stored by the TopBottomTemplateState and sets the changed state as
     * the current state.
     */
    public class TopBottomTemplate extends Template
    {
    // Constants for specifying the selection of elements from the
    // beginning or the end of the result set.
      public static final int TOP_BOTTOM_TYPE_TOP = 0;
      public static final int TOP_BOTTOM_TYPE_BOTTOM = 1;

      // Variable to store the DynamicDefinition.
      private DynamicDefinition _definition;

      /**
       * Creates a TopBottomTemplate with a default type and number values
       * and the specified base dimension.
       */
      public TopBottomTemplate(Source base, DataProvider dataProvider)
      {
        super(new TopBottomTemplateState(base, TOP_BOTTOM_TYPE_TOP, 0),
                                         dataProvider);
        // Create the DynamicDefinition for this Template. Create the
        // TopBottomTemplateGenerator that the DynamicDefinition uses.
        _definition =
        createDynamicDefinition(new TopBottomTemplateGenerator(dataProvider));
      }

      /**
       * Gets the Source produced by the TopBottomTemplateGenerator
       * from the DynamicDefinition.
       */
      public final Source getSource()
      {
        return _definition.getSource();
      }

      /**
       * Gets the Source that is the base of the elements in the result set.
       * Returns null if the state has no base.
       */
      public Source getBase()
      {
        TopBottomTemplateState state = (TopBottomTemplateState) getCurrentState();
        return state.base;
      }

      /**
       * Sets a Source as the base.
       */
      public void setBase(Source base)
      {
         TopBottomTemplateState state = (TopBottomTemplateState) getCurrentState();
        state.base = base;
        setCurrentState(state);
      }

      /**
       * Gets the Source that specifies the measure and the single
       * selections from the dimensions other than the base.
       */
      public Source getCriterion()
      {
        TopBottomTemplateState state = (TopBottomTemplateState) getCurrentState();
        return state.criterion;
      }

      /**
       * Specifies a Source that defines the measure and the single values
       * selected from the dimensions other than the base.
       * The SingleSelectionTemplate produces such a Source.
       */
      public void setCriterion(Source criterion)
      {
        TopBottomTemplateState state = (TopBottomTemplateState) getCurrentState();
        state.criterion = criterion;
        setCurrentState(state);
      }

      /**
       * Gets the type, which is either TOP_BOTTOM_TYPE_TOP or
       * TOP_BOTTOM_TYPE_BOTTOM.
       */
      public int getTopBottomType()
      {
        TopBottomTemplateState state = (TopBottomTemplateState) getCurrentState();
        return state.topBottomType;
      }

      /**
       * Sets the type.
       */
      public void setTopBottomType(int topBottomType)
      {
        if ((topBottomType < TOP_BOTTOM_TYPE_TOP) ||
            (topBottomType > TOP_BOTTOM_TYPE_BOTTOM ))
          throw new IllegalArgumentException("InvalidTopBottomType");
        TopBottomTemplateState state = (TopBottomTemplateState) getCurrentState();
        state.topBottomType = topBottomType;
        setCurrentState(state);
      }

      /**
       * Gets the number of values selected.
       */
      public float getN()
      {
        TopBottomTemplateState state = (TopBottomTemplateState) getCurrentState();
        return state.N;
      }

      /**
       * Sets the number of values to select.
       */
      public void setN(float N)
      {
        TopBottomTemplateState state = (TopBottomTemplateState) getCurrentState();
        state.N = N;
        setCurrentState(state);
      }
    }

Example 11-2 is an implementation of the `TopBottomTemplateState` inner class.

Example 11-2 Implementing a MetadataState

    /**
     * Stores data that can be changed by its TopBottomTemplate.
     * The data is used by a TopBottomTemplateGenerator in producing
     * a Source for the TopBottomTemplate.
     */
    private static final class TopBottomTemplateState
         implements Cloneable, MetadataState
    {
      public int topBottomType;
      public float N;
      public Source criterion;
      public Source base;

      /**
       * Creates a TopBottomTemplateState.
       */
      public TopBottomTemplateState(Source base, int topBottomType, float N)
      {
        this.base = base;
        this.topBottomType = topBottomType;
        this.N = N;
      }

      /**
       * Creates a copy of this TopBottomTemplateState.
       */
      public final Object clone()
      {
        try
        {
          return super.clone();
        }
        catch(CloneNotSupportedException e)
        {
          return null;
        }
      }
    }

Example 11-3 is an implementation of the `TopBottomTemplateGenerator` inner class.

Example 11-3 Implementing a SourceGenerator

    /**
     * Produces a Source for a TopBottomTemplate based on the data
     * values of a TopBottomTemplateState.
     */
    private final class TopBottomTemplateGenerator
          implements SourceGenerator
    {
      // Store the DataProvider.
      private DataProvider _dataProvider;

      /**
       * Creates a TopBottomTemplateGenerator.
       */
      public TopBottomTemplateGenerator(DataProvider dataProvider)
      {
        _dataProvider = dataProvider;
      }

      /**
       * Generates a Source for a TopBottomTemplate using the current
       * state of the data values stored by the TopBottomTemplateState.
       */
      public Source generateSource(MetadataState state)
      {
        TopBottomTemplateState castState = (TopBottomTemplateState) state;
        if (castState.criterion == null)
          throw new NullPointerException("CriterionParameterMissing");
        Source sortedBase = null;
        if (castState.topBottomType == TOP_BOTTOM_TYPE_TOP)
          sortedBase = castState.base.sortDescending(castState.criterion);
        else
          sortedBase = castState.base.sortAscending(castState.criterion);
        return sortedBase.interval(1, Math.round(castState.N));
      }
    }

### Implementing an Application That Uses Templates

After you have stored the selections made by the end user in the `MetadataState` for the `Template`, use the `getSource` method of the `DynamicDefinition` to get the dynamic `Source` created by the `Template`. This section provides an example of an application that uses the `TopBottomTemplate` described in Example 11-1. For brevity, the code does not contain much exception handling.

The `Context10g` class used in the example has methods that do the following:

- Connects to an Oracle Database instance as the specified user.

- Provides the OLAP Catalog metadata objects for the measure and the dimensions selected by the end user.

- Creates `Cursor` objects and displays their values.

Example 11-4 does the following:

- Creates a `Context10g` object and from it gets the `DataProvider` and the `TransactionProvider`.

- From the `Context10g` object, gets the `MdmMeasure` and the `MdmPrimaryDimension` objects that it uses.

- Creates a `SingleSelectionTemplate` for selecting single values from some of the dimensions of the measure. For the code of the `SingleSelectionTemplate` class that this example uses, see Appendix B.

- Creates a `TopBottomTemplate` and stores selections made by the end user.

- Gets the `Source` produced by the `TopBottomTemplate`.

- Uses the `Context10g` object to create a `Cursor` for that `Source` and to display its values.

To use Example 8-2 from Chapter 8, replace the lines in the `run` method from the following comment to the end of the method

    // Replace from here on for the Using Child Transaction example.

You can also get the complete code for the examples from the Oracle OLAP Java API Reference..

Example 11-4 Getting the Source Produced by the Template

    import oracle.olapi.data.source.DataProvider;
    import oracle.olapi.data.source.Source;
    import oracle.olapi.data.source.StringSource;
    import oracle.olapi.transaction.Transaction;
    import oracle.olapi.transaction.TransactionProvider;
    import oracle.olapi.transaction.NotCommittableException;
    import oracle.olapi.metadata.mdm.MdmDimensionMemberInfo;
    import oracle.olapi.metadata.mdm.MdmHierarchy;
    import oracle.olapi.metadata.mdm.MdmMeasure;
    import oracle.olapi.metadata.mdm.MdmPrimaryDimension;

    import java.util.Iterator;
    import java.util.ArrayList;
    import java.util.List;

    /**
     * Creates a query that specifies a number of elements from the top
     * or bottom of a selection of dimension members, creates a Cursor
     * for the query, and displays the values of the Cursor.
     * The selected dimension members are those that have measure values
     * that are specified by selected members of the other dimensions of
     * the measure.
     */
    public class TopBottomTest
    {
      /**
       * Creates a Context10g object that connects to an Oracle Database instance.
       * Gets the MdmMeasure for the Global schema UNITS measure
       * and the MdmPrimaryDimension objects for that measure.
       * Gets the default hierarchies for the dimensions and then gets the Source
       * object for the base of the query.
       * Creates a SingleSelectionTemplate and adds selections to it.
       * Creates a TopBottomTemplate and sets its properties.
       * Gets the Source produced by the TopBottomTemplate, creates a Cursor
       * for it, and displays the values of the Cursor.
       * Changes the state of the SingleSelectionTemplate and the
       * TopBottomTemplate, creates a new Cursor for the Source produced by the
       * TopBottomTemplate, and displays the values of that Cursor.
       */
      public void run(String[] args)
      {
        // Create a Context10g object and get the DataProvider and
        // TransactionProvider from it.
        Context10g context = new Context10g(args, false);
        DataProvider dp = context.getDataProvider();
        TransactionProvider tp = context.getTransactionProvider();

        // Get the MdmMeasure for the measure.
        MdmMeasure mdmUnits = context.getMdmMeasureByName("UNITS");
        // Get the Source for the measure.
        Source units = mdmUnits.getSource();

        // Get the MdmPrimaryDimension objects for the dimensions of the measure.
        MdmPrimaryDimension[] mdmPrimDims =
          context.getMdmPrimaryDimensionsByName(new String[]
                                                    {"CUSTOMER",
                                                     "PRODUCT",
                                                     "CHANNEL",
                                                     "TIME"});
        MdmPrimaryDimension mdmCustDim = mdmPrimDims[0];
        MdmPrimaryDimension mdmProdDim = mdmPrimDims[1];
        MdmPrimaryDimension mdmChanDim = mdmPrimDims[2];
        MdmPrimaryDimension mdmTimeDim = mdmPrimDims[3];

        // Get the default hierarchy of the Product dimension.
        MdmHierarchy mdmProdRollup = mdmProdDim.getDefaultHierarchy();

        // Get the StringSource for the hierarchy.
        StringSource prodRollup = (StringSource) mdmProdRollup.getSource();

        // Create a SingleSelectionTemplate to produce a Source that
        // represents the measure values specified by single members of each of
        // the dimensions of the measure other than the base dimension.
        SingleSelectionTemplate singleSelections =
                            new SingleSelectionTemplate(units, dp);

        // Create MdmDimensionMemberInfo objects for single members of the
        // other dimensions of the measure.
        MdmDimensionMemberInfo timeMemInfo =
             new MdmDimensionMemberInfo(mdmTimeDim, "CALENDAR::YEAR::4");
        MdmDimensionMemberInfo custMemInfo =
             new MdmDimensionMemberInfo(mdmCustDim, "SHIPMENTS_ROLLUP::REGION::8");
        MdmDimensionMemberInfo chanMemInfo =
             new MdmDimensionMemberInfo(mdmChanDim, "CHANNEL_ROLLUP::CHANNEL::2");

        // Add the dimension member information objects to the
        // SingleSelectionTemplate.
        singleSelections.addDimMemberInfo(custMemInfo);
        singleSelections.addDimMemberInfo(chanMemInfo);
        singleSelections.addDimMemberInfo(timeMemInfo);

        // Create a TopBottomTemplate specifying, as the base, the Source for a
        // a hierarchy of a dimension.
        TopBottomTemplate topNBottom = new TopBottomTemplate(prodRollup, dp);

        // Specify whether to retrieve the elements from the beginning (top) or the
        // end (bottom) of the selected elements of the base dimension.
        topNBottom.setTopBottomType(TopBottomTemplate.TOP_BOTTOM_TYPE_TOP);

        // Set the number of elements of the base dimension to retrieve.
        topNBottom.setN(10);
        // Get the Source produced by the SingleSelectionTemplate and specify it as
        // the criterion object.
        topNBottom.setCriterion(singleSelections.getSource());

        // Prepare and commit the current transaction.
        try
        {
          tp.prepareCurrentTransaction();
        }
        catch(NotCommittableException e)
        {
          context.println("Cannot commit the current Transaction. " + e);
        }
        tp.commitCurrentTransaction();

        // Replace from here on for the Using Child Transaction Objects example.

        // Get the short value descriptions of the dimension members from the
        // SingleSelectionTemplate.
        StringBuffer shortDescrsForMemberVals =
                                   singleSelections.getMemberShortDescrs(dp, tp);
        context.println("\nThe " + Math.round(topNBottom.getN()) +
                        " products with the most units sold \nfor" +
                        shortDescrsForMemberVals +" are:\n");

        // Get the Source produced by the TopBottomTemplate, create a Cursor
        // for it, and display the values of the Cursor.
        Source result = topNBottom.getSource();
        context.displayTopBottomResult(result);

        // Change a dimension member selection of the SingleSelectionTemplate.
        timeMemInfo.setUniqueValue("CALENDAR::YEAR::3");
        singleSelections.changeSelection(timeMemInfo);

        // After changing the selection of a dimension member, get the short value
        // descriptions of the dimension members again.
        StringBuffer shortDescrsForMemberValsAfter =
                                    singleSelections.getMemberShortDescrs(dp, tp);

        // Change the number of elements selected and the type of selection.
        topNBottom.setN(5);
        topNBottom.setTopBottomType(TopBottomTemplate.TOP_BOTTOM_TYPE_BOTTOM);

        // Prepare and commit the current transaction.
        try
        {
          tp.prepareCurrentTransaction();
        }
        catch(NotCommittableException e)
        {
          context.println("Cannot commit the current Transaction. " + e);
        }

        tp.commitCurrentTransaction();

        context.println("\nThe " + Math.round(topNBottom.getN()) + " products " +
                      "with the fewest units sold \nfor" +
                        shortDescrsForMemberValsAfter + " are:\n");

        // Create a new Cursor for the Source produced by the TopBottomTemplate
        // and display the Cursor values.
        context.displayTopBottomResult(result);
      }

      /**
       * Runs the TopBottomTest application.
       *
       * @param args An array of String objects that provides the arguments
       *             required to connect to an Oracle Database instance, as
       *             specified in the Context10g class.
       */
      public static void main(String[] args)
      {
        new TopBottomTest().run(args);
      }

}

The `TopBottomTest` program produces the following output.

    The 10 products with the most units sold
    for Asia Pacific, Direct Sales, 2001 are:

     1. PRODUCT_ROLLUP::TOTAL_PRODUCT::1
     2. PRODUCT_ROLLUP::CLASS::3
     3. PRODUCT_ROLLUP::FAMILY::7
     4. PRODUCT_ROLLUP::CLASS::2
     5. PRODUCT_ROLLUP::FAMILY::9
     6. PRODUCT_ROLLUP::FAMILY::6
     7. PRODUCT_ROLLUP::FAMILY::11
     8. PRODUCT_ROLLUP::ITEM::30
     9. PRODUCT_ROLLUP::ITEM::28
    10. PRODUCT_ROLLUP::ITEM::47

    The 5 products with the fewest units sold
    for Asia Pacific, Direct Sales, 2000 are:

    1. PRODUCT_ROLLUP::ITEM::44
    2. PRODUCT_ROLLUP::ITEM::36
    3. PRODUCT_ROLLUP::ITEM::43
    4. PRODUCT_ROLLUP::ITEM::38
    5. PRODUCT_ROLLUP::ITEM::21